//
//  DataBaseHandler.m
//  ShareYOU
//
//  Created by 杨亭 on 14-12-24.
//  Copyright (c) 2014年 ShareYOU. All rights reserved.
//

#import "DataBaseHandler.h"

@implementation DataBaseHandler

+ (DataBaseHandler *)shareInstance
{
    //当第一次执行的时候会产生一个空指针
    static DataBaseHandler *handler = nil;
    
    //对指针进行判断, 当第一次执行的时候创建一个对象
    if (handler == nil) {
        
        handler = [[DataBaseHandler alloc] init];
        
    }
    
    //无论是创建的和已经存在的, 都在这里直接return出去
    return handler;
}

-(BOOL)openDB
{
    //打开数据库
    
    //1.拼接一个数据库文件路径
    
    NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    //在documents文件夹下拼接一个数据库文件地址
    NSString *dbPath = [docPath stringByAppendingPathComponent:@"shareYOU.db"];
    NSLog(@"%@", docPath);
    
    
    //参数1:数据库文件存储的路径(UTF8String可以直接将oc语言字符串转成c语言字符串)
    //参数2:数据库dbPoint指针地址
    //返回值:执行sqlite函数的结果(int类型)
    int result = sqlite3_open([dbPath UTF8String], &dbPoint);
    
    
    if (result == SQLITE_OK ) {
        
        NSLog(@"打开成功");
        return YES;
        
    }else {
        NSLog(@"打开失败");
        return NO;
    }
    

    
}


-(BOOL)closeDB
{
    //关闭数据库
    
    int result = sqlite3_close(dbPoint);
    
    //利用封装的方法判断结果
    return [self judgeResult:result text:@"关闭"];
}

//判断结果的方法
- (BOOL)judgeResult:(int)result text:(NSString *)text
{
    if (result == SQLITE_OK) {
        
        NSLog(@"%@成功", text);
        return YES;
        
    }
    else{
        NSLog(@"%@失败", text);
        NSLog(@"错误编码: %d", result);
        
        return NO;
    }
    
}

//创建类型表
- (BOOL)createTable
{
    NSString *sql = @"create table PayoutType(type text primary key)";
    
    //执行sql语句的函数
    
    //参数1:数据库指针
    //参数2:sql语句
    int result = sqlite3_exec(dbPoint, [sql UTF8String], NULL, NULL, NULL);
    
    //判断数据库是否为空,如果为空则填入以下数据
     DataBaseHandler *dataHandler = [DataBaseHandler shareInstance];
    if ([[dataHandler selectAllT] count] < 1)
    {

        //添加数据
        Payout *payout1 = [[Payout alloc] init];
        payout1.type = @"衣服饰品";
        [dataHandler insertTypePayout:payout1];
        
        Payout *payout2 = [[Payout alloc] init];
        payout2.type = @"食品酒水";
        [dataHandler insertTypePayout:payout2];
        
        Payout *payout3 = [[Payout alloc] init];
        payout3.type = @"通讯交流";
        [dataHandler insertTypePayout:payout3];
        
        Payout *payout4 = [[Payout alloc] init];
        payout4.type = @"人情往来";
        [dataHandler insertTypePayout:payout4];
        
        [payout1 release];
        [payout2 release];
        [payout3 release];
        [payout4 release];
    }

    
    return [self judgeResult:result text:@"创建类型表"];
    
}

//删除类型表
- (BOOL)deleteTable
{
    NSString *sql = @"delete from PayoutType";
    
    //执行sql语句的函数
    
    //参数1:数据库指针
    //参数2:sql语句
    int result = sqlite3_exec(dbPoint, [sql UTF8String], NULL, NULL, NULL);
    
    [self createTable];
    
    return [self judgeResult:result text:@"删除类型表"];
}



//创建成员表
- (BOOL)createPersonnelTable
{
    NSString *sql = @"create table PayoutPersonnel(personnel text primary key)";
    
    //执行sql语句的函数
    
    //参数1:数据库指针
    //参数2:sql语句
    int result = sqlite3_exec(dbPoint, [sql UTF8String], NULL, NULL, NULL);
    
    //判断数据库是否为空,如果为空则填入以下数据
    DataBaseHandler *dataHandler = [DataBaseHandler shareInstance];
    if ([[dataHandler selectAllP] count] < 1) {
        //添加数据
        Payout *payout1 = [[Payout alloc] init];
        payout1.personnel = @"老公";
        [dataHandler insertPersonnelPayout:payout1];
        
        Payout *payout2 = [[Payout alloc] init];
        payout2.personnel = @"老婆";
        [dataHandler insertPersonnelPayout:payout2];
        
        Payout *payout3 = [[Payout alloc] init];
        payout3.personnel = @"儿子";
        [dataHandler insertPersonnelPayout:payout3];
        
        Payout *payout4 = [[Payout alloc] init];
        payout4.personnel = @"女儿";
        [dataHandler insertPersonnelPayout:payout4];
        
        Payout *payout5 = [[Payout alloc] init];
        payout5.personnel = @"朋友";
        [dataHandler insertPersonnelPayout:payout5];
        
        [payout1 release];
        [payout2 release];
        [payout3 release];
        [payout4 release];
        [payout5 release];
        
    }
    
    return [self judgeResult:result text:@"创建成员表"];
    
}

//删除成员表
- (BOOL)deletePersonnelTable
{
    NSString *sql = @"delete from PayoutPersonnel";
    
    //执行sql语句的函数
    
    //参数1:数据库指针
    //参数2:sql语句
    int result = sqlite3_exec(dbPoint, [sql UTF8String], NULL, NULL, NULL);
    
    [self createPersonnelTable];
    
    return [self judgeResult:result text:@"删除成员表"];
}



//插入类型
- (BOOL)insertTypePayout:(Payout *)payout
{
    //拼接sql语句的时候注意:字符串要以单引号(')标记, 其他的都不标记
    NSString *sql = [NSString stringWithFormat:@"insert into PayoutType values('%@')", payout.type];
    
    //执行sql语句, 判断结果
    
    int result = sqlite3_exec(dbPoint, [sql UTF8String], NULL, NULL, NULL);
    
    return [self judgeResult:result text:@"添加类型数据"];
    
    
}
//插入成员
- (BOOL)insertPersonnelPayout:(Payout *)payout
{
    //拼接sql语句的时候注意:字符串要以单引号(')标记, 其他的都不标记
    NSString *sql = [NSString stringWithFormat:@"insert into PayoutPersonnel values('%@')", payout.personnel];
    
    //执行sql语句, 判断结果
    
    int result = sqlite3_exec(dbPoint, [sql UTF8String], NULL, NULL, NULL);
    
    return [self judgeResult:result text:@"添加成员数据"];
    
    
}


//查找类型
- (NSMutableArray *)selectAllT
{
    //1.创建一个数据库的替身, 存储对数据库的所有操作
    
    sqlite3_stmt *stmt = nil;
    
    //2.准备sql语句并执行, 将结果保存在stmt中
    
    NSString *sql = @"select * from PayoutType";
    
    //参数1:数据库指针
    //参数2:sql语句
    //参数3:限制sql语句的长度(如果是-1的话, 就是不限制)
    //参数4:替身指针的地址
    //作用:让数据库执行sql语句, 并把结果保存到stmt中
    int result = sqlite3_prepare_v2(dbPoint, [sql UTF8String], -1, &stmt, NULL);
    
    //3.创建一个可变数组, 用于存储数据
    
    NSMutableArray *payoutArr = [NSMutableArray array];
    
    //4.去遍历所有的数据, 创建相应的对象
    
    if (result == SQLITE_OK) {
        
        //当sql执行成功, 遍历数据
        //循环遍历所有的结果, 每次遍历到一条数据, 都会返回SQLITE_ROW, 如果没有数据了, 就不会返回SQLITE_ROW, 跳出循环
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            
            
            //在循环体中去按例取数据
            
            const unsigned char *addType = sqlite3_column_text(stmt, 0);
            NSString *type = [NSString stringWithUTF8String:(const char *)addType];
            
            Payout *payout = [[Payout alloc] init];
            //拿获取到得数据 给对象赋值
            
          
            payout.type = type;
            
            //将对象添加到数组中
            [payoutArr addObject:payout];
            [payout release];
            
        }
        
    }
    //销毁stmt替身, 把里面的操作和结果写入本地sqlite文件
    sqlite3_finalize(stmt);
    
    return payoutArr;
}


//查找成员
- (NSMutableArray *)selectAllP
{
    //1.创建一个数据库的替身, 存储对数据库的所有操作
    
    sqlite3_stmt *stmt = nil;
    
    //2.准备sql语句并执行, 将结果保存在stmt中
    
    NSString *sql = @"select * from PayoutPersonnel";
    
    //参数1:数据库指针
    //参数2:sql语句
    //参数3:限制sql语句的长度(如果是-1的话, 就是不限制)
    //参数4:替身指针的地址
    //作用:让数据库执行sql语句, 并把结果保存到stmt中
    int result = sqlite3_prepare_v2(dbPoint, [sql UTF8String], -1, &stmt, NULL);
    
    //3.创建一个可变数组, 用于存储数据
    
    NSMutableArray *payoutArr = [NSMutableArray array];
    
    //4.去遍历所有的数据, 创建相应的对象
    
    if (result == SQLITE_OK) {
        
        //当sql执行成功, 遍历数据
        //循环遍历所有的结果, 每次遍历到一条数据, 都会返回SQLITE_ROW, 如果没有数据了, 就不会返回SQLITE_ROW, 跳出循环
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            
            
            //在循环体中去按例取数据
            
            const unsigned char *addPerson = sqlite3_column_text(stmt, 0);
            NSString *personel = [NSString stringWithUTF8String:(const char *)addPerson];
            
            
            Payout *payout = [[Payout alloc] init];
            //拿获取到得数据 给对象赋值
            
            payout.personnel = personel;
    
            
            //将对象添加到数组中
            [payoutArr addObject:payout];
            [payout release];
            
        }
        
    }
    //销毁stmt替身, 把里面的操作和结果写入本地sqlite文件
    sqlite3_finalize(stmt);
    
    return payoutArr;
}

//创建记录表
- (BOOL)createWriteTable
{
    NSString *sql = @"create table PayoutWrite(amount text, date text, type text, comment text, personnel text)";
    
    //执行sql语句的函数
    
    //参数1:数据库指针
    //参数2:sql语句
    int result = sqlite3_exec(dbPoint, [sql UTF8String], NULL, NULL, NULL);
    
    return [self judgeResult:result text:@"创建表"];
}

//删除记录表
- (BOOL)deleteWriteTable
{
    NSString *sql = @"delete from PayoutWrite";
    
    //执行sql语句的函数
    
    //参数1:数据库指针
    //参数2:sql语句
    int result = sqlite3_exec(dbPoint, [sql UTF8String], NULL, NULL, NULL);
    
    [self createWriteTable];
    
    return [self judgeResult:result text:@"删除记录表"];
}



//插入记录表
- (BOOL)insertWritePayout:(Payout *)payout
{
    //拼接sql语句的时候注意:字符串要以单引号(')标记, 其他的都不标记
    NSString *sql = [NSString stringWithFormat:@"insert into PayoutWrite values('%@', '%@', '%@', '%@', '%@')", payout.amount, payout.date, payout.type, payout.comment, payout.personnel];
    
    //执行sql语句, 判断结果
    
    int result = sqlite3_exec(dbPoint, [sql UTF8String], NULL, NULL, NULL);
    
    return [self judgeResult:result text:@"添加显示数据"];
    
    
}



//查找记录
- (NSMutableArray *)selectAll
{
    //1.创建一个数据库的替身, 存储对数据库的所有操作
    
    sqlite3_stmt *stmt = nil;
    
    //2.准备sql语句并执行, 将结果保存在stmt中
    
    NSString *sql = @"select * from PayoutWrite";
    
    //参数1:数据库指针
    //参数2:sql语句
    //参数3:限制sql语句的长度(如果是-1的话, 就是不限制)
    //参数4:替身指针的地址
    //作用:让数据库执行sql语句, 并把结果保存到stmt中
    int result = sqlite3_prepare_v2(dbPoint, [sql UTF8String], -1, &stmt, NULL);
    
    //3.创建一个可变数组, 用于存储数据
    
    NSMutableArray *payoutArr = [NSMutableArray array];
    
    //4.去遍历所有的数据, 创建相应的对象
    
    if (result == SQLITE_OK) {
        
        //当sql执行成功, 遍历数据
        //循环遍历所有的结果, 每次遍历到一条数据, 都会返回SQLITE_ROW, 如果没有数据了, 就不会返回SQLITE_ROW, 跳出循环
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            
            
            //在循环体中去按例取数据
            
//            int payout_ID = sqlite3_column_int(stmt, 0);

            const unsigned char *addAmount = sqlite3_column_text(stmt, 0);
            NSString *amount = [NSString stringWithUTF8String:(const char *)addAmount];
            
            const unsigned char *addDate = sqlite3_column_text(stmt, 1);
            NSString *date = [NSString stringWithUTF8String:(const char *)addDate];
            
            const unsigned char *addType = sqlite3_column_text(stmt, 2);
            NSString *type = [NSString stringWithUTF8String:(const char *)addType];

            
            const unsigned char *addComment = sqlite3_column_text(stmt, 3);
            NSString *comment = [NSString stringWithUTF8String:(const char *)addComment];

            
            const unsigned char *addPerson = sqlite3_column_text(stmt, 4);
            NSString *personel = [NSString stringWithUTF8String:(const char *)addPerson];

            
            Payout *payout = [[Payout alloc] init];
            //拿获取到得数据 给对象赋值
            
//            payout.payout_ID = payout_ID;
            payout.amount = amount;
            payout.date = date;
            payout.type = type;
            payout.comment = comment;
            payout.personnel = personel;
            
            //将对象添加到数组中
            [payoutArr addObject:payout];
            [payout release];
            
        }
        
    }
    //销毁stmt替身, 把里面的操作和结果写入本地sqlite文件
    sqlite3_finalize(stmt);
    
    return payoutArr;
}








////删除一笔帐
//-(void)deletePayout:(int)payout_ID{
//    NSString *delete=[NSString stringWithFormat:@"DELETE FROM payout WHERE ID = %d",payout_ID];
//}
//
////删除数据
//- (BOOL) deleteTestList:(sqlTestList *)deletList{
//    if ([self openDB]) {
//        
//        sqlite3_stmt *statement;
//        //组织SQL语句
//        static char *sql = "delete from testTable  where testID = ? and testValue = ? and testName = ?";
//        //将SQL语句放入sqlite3_stmt中
//        int success = sqlite3_prepare_v2(_database, sql, -1, &statement, NULL);
//        if (success != SQLITE_OK) {
//            NSLog(@"Error: failed to delete:testTable");
//            sqlite3_close(_database);
//            return NO;
//        }
//        
//        //这里的数字1，2，3代表第几个问号。这里只有1个问号，这是一个相对比较简单的数据库操作，真正的项目中会远远比这个复杂
//        sqlite3_bind_int(statement, 1, deletList.sqlID);
//        sqlite3_bind_text(statement, 2, [deletList.sqlText UTF8String], -1, SQLITE_TRANSIENT);
//        sqlite3_bind_text(statement, 3, [deletList.sqlname UTF8String], -1, SQLITE_TRANSIENT);
//        //执行SQL语句。这里是更新数据库
//        success = sqlite3_step(statement);
//        //释放statement
//        sqlite3_finalize(statement);
//        
//        //如果执行失败
//        if (success == SQLITE_ERROR) {
//            NSLog(@"Error: failed to delete the database with message.");
//            //关闭数据库
//            sqlite3_close(_database);
//            return NO;
//        }
//        //执行成功后依然要关闭数据库
//        sqlite3_close(_database);
//        return YES;
//    }
//    return NO;
//    
//}
//
//
//
//

////修改一笔帐
//-(void)alterPayout:(payout*)payout{
//    //    payout.payout_ID=14;
//    NSString*amount=[NSString stringWithFormat:@"UPDATE payout SET amount = %f WHERE ID = %d",payout.amount,payout.payout_ID];
//    [Fdb executeUpdate:amount];
//    NSString*date=[NSString stringWithFormat:@"UPDATE payout SET date = '%@' WHERE ID = %d",payout.date,payout.payout_ID];
//    [Fdb executeUpdate:date];
//    NSString*type=[NSString stringWithFormat:@"UPDATE payout SET type = '%@' WHERE ID = %d",payout.type,payout.payout_ID];
//    [Fdb executeUpdate:type];
//    NSString*subtype=[NSString stringWithFormat:@"UPDATE payout SET subtype = '%@' WHERE ID = %d",payout.subType,payout.payout_ID];
//    [Fdb executeUpdate:subtype];
//    NSString*comment=[NSString stringWithFormat:@"UPDATE payout SET comment = '%@' WHERE ID = %d",payout.comment,payout.payout_ID];
//    [Fdb executeUpdate:comment];
//    NSString*image=[NSString stringWithFormat:@"UPDATE payout SET image = ? WHERE ID = %d",payout.payout_ID];
//    [Fdb executeUpdate:image,payout.image];
//    NSString*personnel=[NSString stringWithFormat:@"UPDATE payout SET personnel = '%@' WHERE ID = %d",payout.personnel,payout.payout_ID];
//    [Fdb executeUpdate:personnel];
//    
//}





@end
